<?php
class SeminarVillageExport_Controller extends AdminController {
	
	public function __construct() {
		parent::__construct('seminar');
	}
	
	public function index() {
		$seminar = $this->getSeminar();
		
		$this->viewData['seminar'] = $seminar;
		$this->viewData['programTypeOptions'] = $this->dao->queryOptions(
				'SELECT l10n.program_type_code, l10n.name ' .
				'FROM available_program_type av ' .
				'JOIN program_type pt ON pt.code = av.program_type_code AND pt.with_accommodation = :true ' .
				'JOIN program_type_l10n l10n ON av.program_type_code = l10n.program_type_code AND l10n.language_code = :lang ' .
				'WHERE av.seminar_id = :seminar_id ' .
				'ORDER BY l10n.name',
				array('lang' => $this->getL10nLanguageCode(), 'seminar_id' => $seminar['id'], 'true' => sql::$TRUE));

		// Default values
		$this->viewData['formData'] = array(
				'id' => $seminar['id'],
				'program_type_codes' => array_keys($this->viewData['programTypeOptions']));
		
		$this->renderSubview('admin/seminar/seminarVillageExport');
	}
	
	private function getSeminar() {
		$seminarId = $this->input->get('id');
		if (! $seminarId) {
			$seminarId = $this->input->post('id');
		}
		if (! valid::isId($seminarId)) {
			$this->redirectToList('error-invalidId');
		}
		 
		$seminar = $this->dao->fetchSeminar($seminarId, $this->getL10nLanguageCode());
		if (! $seminar) {
			$this->redirectToList('error-recordNotFound');
		}
		$this->viewData['seminar'] = $seminar;
		return $seminar;
	}
	
	public function export() {
		$seminar = $this->getSeminar();
		$programTypeCodes = $_POST['program_type_codes'];
	
		// -----
		 
		excel::loadClasses();
		 
		$objPHPExcel = new PHPExcel();
		 
		// Metadata
		excel::setMetadata($objPHPExcel);
		$objPHPExcel->setActiveSheetIndex(0);
		$sheet = $objPHPExcel->getActiveSheet();
		 
		$columnLetters = excel::columnLetters();
		 
		$titleStyle = array(
				'font' => array(
						'bold' => FALSE,
						'size' => 14,
				),
				'alignment' => array(
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
						'wrap' => FALSE,
				),
		);
		 
		$headerStyle = array(
				'borders' => array(
						'outline' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
								'color' => array('argb' => 'FF000000'),
						),
				),
				'font' => array(
						'bold' => TRUE,
				),
				'alignment' => array(
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
						'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
				),
		);
		 
		$dataStyle = array(
				'borders' => array(
						'outline' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
								'color' => array('argb' => 'FF000000'),
						),
						'inside' => array(
								'style' => PHPExcel_Style_Border::BORDER_HAIR,
								'color' => array('argb' => 'FF606060'),
						),
				),
		);
		 
		$dataCellStyle = array(
				'font' => array(
						'bold' => FALSE,
				),
				'alignment' => array(
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
						'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
				),
		);
		 
		$nameStyle = $dataCellStyle;
		$nameStyle['alignment'] = array(
				'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,
		);
		 
		$summaryStyle = array(
				'font' => array(
						'bold' => true,
				),
				'alignment' => array(
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
						'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
				),
				'borders' => array(
						'outline' => array(
								'style' => PHPExcel_Style_Border::BORDER_THIN,
								'color' => array('argb' => 'FF000000'),
						),
				),
				'fill' => array(
						'type' => PHPExcel_Style_Fill::FILL_SOLID,
						'startcolor' => array('argb' => 'FFFFFFD0'),
				),
		);
		 
		$summaryRowTitleStyle = array(
				'font' => array(
						'bold' => TRUE,
				),
				'alignment' => array(
						'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT,
						'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
				),
		);
		 
		$columnLetters = excel::columnLetters();
		 
		$rowNumber = 1;
		$columnNumber = 0;
		 
		/*---------------------------------
		 * Headers
		*---------------------------------*/
		 
		$sheet->setCellValue('A' . $rowNumber, $seminar['name'] . ', ' . format::date($seminar['begin_date']) . ' - ' . format::date($seminar['end_date']));
		$sheet->mergeCells('A' . $rowNumber . ':' . 'H' . $rowNumber);
		$sheet->getStyle('A' . $rowNumber)->applyFromArray($titleStyle);
		$rowNumber += 2;
		 
		$headerKeyList = array('person.full_name', 'person.place_of_residence',
				'person.idcard_number', 'viRegistration.night_count',
				'viSeminar.recreationFee', 'viSeminar.accomodationFee');
		foreach ($headerKeyList as $headerKey) {
			$sheet->setCellValue($columnLetters[$columnNumber++] . $rowNumber, text::get($headerKey));
		}
		 
		$lastColumn = $columnLetters[$columnNumber];
		 
		foreach ($columnLetters as $column) {
			if ($column == $lastColumn) {
				break;
			}
			$sheet->getStyle($column . $rowNumber)->applyFromArray($headerStyle);
		}
		 
		$rowNumber++;
		 
		/*---------------------------------
		 * Data
		 *---------------------------------*/
		 
		$firstDataRow = $rowNumber;
		 
		// Fees for village
		$seminarEnd = strtotime($seminar['end_date']);
		$date18YearsAgo = (date('Y', $seminarEnd) - 18) . date('-m-d', $seminarEnd);
		$date70YearsAgo = (date('Y', $seminarEnd) - 70) . date('-m-d', $seminarEnd);
	
		$registrationList = $this->dao->queryAll(
				'SELECT reg.*, per.first_name, per.last_name, per.other_names, per.title, ' .
				'per.birth_date, per.street, per.city, per.postal_code, per.country_code, ctry.name AS country_name, per.idcard_number, b.begin_date, b.end_date ' .
				'FROM registration reg ' .
				'JOIN person per ON per.id = reg.person_id ' .
				'JOIN country ctry ON ctry.code = per.country_code ' .
				'JOIN category c ON c.category_code = reg.category_code AND reg.seminar_id = c.seminar_id ' .
				'JOIN batch b ON b.batch_code = reg.batch_code AND b.seminar_id = reg.seminar_id ' .
				'WHERE reg.seminar_id = :seminar_id ' .
				'AND reg.is_present = :is_present ' .
				'AND reg.cancelled_at IS NULL ' .
				'AND per.birth_date < :birth_date_lt ' .
				'AND per.birth_date > :birth_date_gt ' .
				'AND c.accomodation_type_code != :acc_code ' .
				'AND c.program_type_code IN (' . sql::inClauseValues($programTypeCodes) . ') ' .
				'ORDER BY per.last_name, per.first_name',
				array(
						'is_present' => sql::$TRUE,
						'seminar_id' => $seminar['id'],
						'acc_code' => AccommodationTypeEntity::$CODE_WITHOUT,
						'birth_date_lt' => $date18YearsAgo,
						'birth_date_gt' => $date70YearsAgo
				));
		 
		foreach ($registrationList as $registration) {
			$registration['night_count'] = date::nightCount($registration['begin_date'], $registration['end_date']);
			excel::writeVillageRecord($sheet, $registration, $rowNumber, $columnLetters, $dataCellStyle, $nameStyle, $lastColumn);
			$rowNumber++;
		}
		
		$lastDataRow = $rowNumber - 1;
		 
		/*---------------------------------
		 * Summary
		 *---------------------------------*/
		 
		$rowNumber++;
		$columnNumber = 2;
		 
		 
		$sheet->setCellValue('A' . $rowNumber, text::get('viSeminar.villageFees-sumTotal') . ':');
		$sheet->mergeCells('A' . $rowNumber . ':' . $columnLetters[$columnNumber] . $rowNumber);
		$sheet->getStyle('A' . $rowNumber)->applyFromArray($summaryRowTitleStyle);
		 
		$columnNumber++;
		for ($i = 0; $i < 3; $i++) {
			$col = $columnLetters[$columnNumber++];
			$sheet->setCellValue($col . $rowNumber, '=SUM(' . $col . $firstDataRow . ':' . $col . ($rowNumber - 2) . ')');
			$sheet->getStyle($col . $rowNumber)->applyFromArray($summaryStyle);
		}
		 
		/*---------------------------------
		 * Styling
		 *---------------------------------*/
		 
		foreach ($columnLetters as $column) {
			if ($column == $lastColumn) {
				break;
			}
			$sheet->getStyle($column . $firstDataRow .':' . $column . $lastDataRow)->applyFromArray($dataStyle);
		}
		 
		$columnWidths = array(24, 56, 20, 12, 20, 20);
		foreach ($columnWidths as $columnNumber => $width) {
			$sheet->getColumnDimension($columnLetters[$columnNumber])->setWidth($width);
		}
		 
		// Set the worksheet’s page orientation and size can be done using the following lines
		$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
		$sheet->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
		 
		$objPHPExcel->setActiveSheetIndex(0);
		 
		// Save the file
		$fileName = tempNam(Kohana::config('yosemin.temp_dir'), 'yoseminXlsx');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		$objWriter->save($fileName);
		 
		download::force($fileName, NULL, $seminar['name'] . '.xlsx');
	}
	
	protected function redirectToList($errorKey=NULL) {
		if ($errorKey) {
			$this->msg->error($errorKey);
		}
		$this->redirect('/admin/seminar/all');
	}
}